package com.zsy.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zsy.bean.Article;
import com.zsy.bean.Keyword;
import com.zsy.bean.ParseBBSConfig;
import com.zsy.bean.PrefixCrawlConfig;
import com.zsy.bean.Site;
import com.zsy.util.DBUtil;

public class BBSService {
	/**
	 * 根据id删除bbs配置信息
	 * @param id
	 * @throws SQLException
	 */
	public void deleteBbsConfig(String id) throws SQLException {
		Connection conn = DBUtil.getInstance().getConnection();
		StringBuilder sb = new StringBuilder("delete from bbs_config where id=");
		sb.append(id);
		conn.createStatement().execute(sb.toString());
		conn.close();
	}
	/**
	 * 删除前缀抓取配置信息
	 * @param id
	 * @param prefixId
	 * @throws SQLException
	 */
	public void deletePrefix(String id,String prefixId) throws SQLException {
		Connection conn = DBUtil.getInstance().getConnection();
		StringBuilder sb = new StringBuilder("delete from prefix_crawl_config where id=");
		sb.append(id);
		conn.createStatement().execute(sb.toString());
		sb = new StringBuilder("delete from prefix where id=");
		sb.append(prefixId);
		conn.createStatement().execute(sb.toString());
		conn.close();
	}
	/**
	 * 获取前缀配置列表
	 * @return
	 * @throws SQLException
	 */
	public List<PrefixCrawlConfig> getPrefixCrawlConfigList() throws SQLException{
		List<PrefixCrawlConfig> prefixList = new ArrayList();
		StringBuilder sb = new StringBuilder("select pcc.id id, s.site_ch,p.prefix,p.id prefixId from prefix_crawl_config pcc ,prefix p,site s where pcc.prefixId=p.id and pcc.siteId=s.id order by id desc");
		Connection conn = DBUtil.getInstance().getConnection();
		ResultSet rs = conn.createStatement().executeQuery(sb.toString());
		PrefixCrawlConfig pcc ;
		while(rs.next()){
			pcc = new PrefixCrawlConfig();
			pcc.setId(rs.getInt(1));
			pcc.setSite(rs.getString(2));
			pcc.setPrefix(rs.getString(3));
			pcc.setPrefixId(rs.getInt(4));
			prefixList.add(pcc);
		}
		return prefixList;
	}
	/**
	 * 获取所有bbs配置信息
	 * @return
	 * @throws SQLException
	 */
	public List<ParseBBSConfig> getParseBBSConfigList() throws SQLException{
		List<ParseBBSConfig> bbsList = new ArrayList();
		StringBuilder sb = new StringBuilder();
		sb.append("select bbs_config.id id,site,body_regex, next_regex from site,bbs_config where site.id=bbs_config.siteId order by  id  desc");
		Connection conn = DBUtil.getInstance().getConnection();
		ResultSet rs = conn.createStatement().executeQuery(sb.toString());
		ParseBBSConfig pbbs ;
		while(rs.next()){
			pbbs = new ParseBBSConfig();
			pbbs.setId(rs.getInt(1));
			pbbs.setSiteName(rs.getString(2));
			pbbs.setBody_regex(rs.getString(3));
			pbbs.setNext_regex(rs.getString(4));
			bbsList.add(pbbs);
		}
		conn.close();
		rs.close();
		return bbsList;
	}

	
	/**
	 * 保存前缀配置文件信息
	 * @param site
	 * @param prefix
	 * @param bbsRegex
	 * @param encode
	 * @param structRegex
	 * @param beginUrl
	 * @param cookie
	 * @param isBBS
	 * @return
	 * @throws SQLException
	 */
	public boolean savePrefixConf(String site, String prefix, String bbsRegex,
			String encode, String structRegex, String beginUrl,
			String filename, String isBBS,String urlPrefixRegex) throws SQLException {
		Connection conn = DBUtil.getInstance().getConnection();
		StringBuilder sb = new StringBuilder();
		int prefixid = 0;
		PreparedStatement psmt;
		ResultSet rs=null;
		try{
			sb.append("insert into prefix(prefix) values('");
			sb.append(prefix);
			sb.append("')");
			psmt = conn.prepareStatement(sb.toString());
			psmt.execute(sb.toString());
		    rs = psmt.executeQuery("select max(id) id from prefix");
		}catch(Exception e){
			//如果前缀已经配置
			sb.append("select id from prefix where prefix='");
			sb.append(prefix);
			sb.append("'");
			psmt = conn.prepareStatement(sb.toString());
			rs = psmt.executeQuery(sb.toString());
		}
		while(rs.next()){
			prefixid = rs.getInt(1);
		}
		String [] urlArr = beginUrl.split(",");
		sb = new StringBuilder();
		sb.append("insert into prefix_crawl_config (siteid,prefixId,struct_regex,bbsprefix_regex,type,beginurl,file_name,urlprefix_regex) values(?,?,?,?,?,?,?,?)");
		psmt = conn.prepareStatement(sb.toString());
		psmt.setString(1, site);
		psmt.setObject(2, prefixid);
		psmt.setObject(3, structRegex);
		psmt.setObject(4, bbsRegex);
		psmt.setString(5, isBBS);
		psmt.setString(6, urlArr[0]);
		psmt.setString(7, filename);
		psmt.setString(8, urlPrefixRegex);
		psmt.execute();
		
		
		for(int i=0;i<urlArr.length;i++){
			sb = new StringBuilder();
			sb.append("insert into crawlurl(prefixId,url,siteId) values(?,?,?)");
			psmt = conn.prepareStatement(sb.toString());
			psmt.setInt(1, prefixid);
			psmt.setString(2, urlArr[i]);
			psmt.setString(3,site);
			psmt.execute();
		}
		
		if(null !=conn){
			conn.close();
		}
		if(null !=psmt){
			psmt.close();
		}
		if(null !=rs){
			rs.close();
		}
		return true;

	}
	/**
	 * 保存解析bbs的配置
	 * @param browse_regex
	 * @param next_regex
	 * @param reply_regex
	 * @param body_regex
	 * @param prefix
	 * @param siteid
	 * @param encode
	 * @param replycount_regex
	 * @throws SQLException
	 */
	public void saveParseBBSConfig(String browse_regex, String next_regex,
			String reply_regex, String body_regex,
			String siteid, String encode, String replycount_regex,String publish_regex,String author_regex) throws SQLException {
		StringBuilder sb = new StringBuilder();
		sb
				.append("insert into bbs_config (browsecount_regex,next_regex,reply_regex,body_regex,siteid,encode,replycount_regex,publishtime_regex,author_regex) values(?,?,?,?,?,?,?,?,?)");
		Connection conn = DBUtil.getInstance().getConnection();
		PreparedStatement psmt = conn.prepareStatement(sb.toString());
		psmt.setString(1, browse_regex);
		psmt.setObject(2, next_regex);
		psmt.setObject(3, reply_regex);
		psmt.setObject(4, body_regex);
		psmt.setString(5, siteid);
		psmt.setString(6, encode);
		psmt.setObject(7, replycount_regex);
		psmt.setString(8, publish_regex);
		psmt.setString(9, author_regex);
		psmt.execute();
		conn.close();
		psmt.close();
	}
	/**
	 * 获取系统的关键字
	 * @return
	 * @throws SQLException
	 */
	public List getKeywords() throws SQLException {
		Keyword keyword;
		List<Keyword> keywordList = new ArrayList();
		Connection conn = DBUtil.getInstance().getConnection();
		String key;
		int id;
		String sql = "select id, keyword from keyword where type=1 limit 0,19";
		ResultSet rs = conn.createStatement().executeQuery(sql);
		while (rs.next()) {
			keyword = new Keyword();
			id = rs.getInt(1);
			key = rs.getString(2);
			keyword.setId(id);
			keyword.setKeyword(key);
			keywordList.add(keyword);
		}
		rs.close();
		conn.close();
		return keywordList;
	}

	/**
	 * 
	 * @param type 0 为bbs定制的关键字 1为搜索引擎的关键字
	 * @param page
	 * @return
	 * @throws SQLException 
	 */
	public List showAllKeyword(int type,int page) throws SQLException{
		List<Keyword> list = new ArrayList();
		int pagesize=30;
		int start=(page-1)*pagesize;
		int end = start+pagesize;
		StringBuilder sb = new StringBuilder();
		sb.append("select id,keyword,type from keyword where type='");
		sb.append(type);
		sb.append("'");
		sb.append("limit ");
		sb.append(start);
		sb.append(",");
		sb.append(end);
		try
		{
		Connection conn = DBUtil.getInstance().getConnection();
		PreparedStatement psmt = conn.prepareStatement(sb.toString());
		ResultSet rs = psmt.executeQuery();
		Keyword keyword;
		while(rs.next()){
			keyword = new Keyword();
			keyword.setId(rs.getInt("id"));
			keyword.setKeyword(rs.getString("keyword"));
			keyword.setType(rs.getInt("type"));
			list.add(keyword);
		}
		psmt.close();
		rs.close();
		conn.close();
		}
		catch(Exception ee)
		{
			System.out.println("Exception when get Keyword");
			ee.printStackTrace();
		}
		return list;
	}
	/**
	 * 保存网站信息
	 * @param site_ch
	 * @param site_en
	 * @throws SQLException
	 */
	public void saveSite(String site_ch,String site_en) throws SQLException{
		StringBuilder sb = new StringBuilder();
		sb.append("insert into site(site,site_ch) values(?,?)");
		Connection conn = DBUtil.getInstance().getConnection();
		PreparedStatement pstmt = conn.prepareStatement(sb.toString());
		pstmt.setString(1, site_en);
		pstmt.setString(2, site_ch);
		pstmt.execute();
		conn.close();
		pstmt.close();
	}
	/**
	 * 保存关键字
	 * @param type
	 * @param keyword
	 * @throws SQLException
	 */
	public void saveKey(String type,String keyword) throws SQLException{
		StringBuilder sb = new StringBuilder();
		sb.append("insert into keyword(type,keyword) values('");
		sb.append(type);
		sb.append("','");
		sb.append(keyword);
		sb.append("')");
		System.out.println(sb.toString());
		Connection conn = DBUtil.getInstance().getConnection();
		System.out.println(sb.toString());
		PreparedStatement psmt = conn.prepareStatement(sb.toString());
		psmt.execute();
		psmt.close();
		conn.close();
	}
	
	/**
	 * 删除关键字
	 * @param id
	 * @throws SQLException
	 */
	public void deleteKey(String id) throws SQLException{
		StringBuilder sb = new StringBuilder();
		sb.append("delete from keyword where id=");
		sb.append(id);
		Connection conn = DBUtil.getInstance().getConnection();
		PreparedStatement psmt = conn.prepareStatement(sb.toString());
		psmt.execute(sb.toString());
		conn.close();
		psmt.close();
	}
	/**
	 * 获取所有网站
	 * @return
	 * @throws SQLException
	 */
	public List getAllSite() throws SQLException{
		List<Site> siteList = new ArrayList();
		StringBuilder sb = new StringBuilder();
		sb.append("select id,site,site_ch from site order by  id  desc");
		Connection conn = DBUtil.getInstance().getConnection();
		PreparedStatement psmt = conn.prepareStatement(sb.toString());
		ResultSet rs = psmt.executeQuery();
		Site site;
		while (rs.next()) {
			site = new Site();
			site.setId(rs.getInt("id"));
			site.setSite_ch(rs.getString("site_ch"));
			site.setSite_en(rs.getString("site"));
			siteList.add(site);
		}
		return siteList;
	}
//////////////////////////////////这些方法目前没有使用/////////////////////////////
	/**
	 * 获取网页文本信息列表
	 * @param page
	 * @return
	 * @throws SQLException
	 */
	public List<Article> getArticleList(int page) throws SQLException {
		List<Article> articleList = new ArrayList();
		Connection conn = DBUtil.getInstance().getConnection();
		Article article;
		String sql = "SELECT title, IF(CHAR_LENGTH(content)>100,CONCAT(LEFT(content,100),'...'),content) ,id FROM article order by updatetime desc limit "
				+ (page - 1) * 20 + ",19";
		System.out.println(sql);
		ResultSet rs = conn.createStatement().executeQuery(sql);
		while (rs.next()) {
			article = new Article();
			article.setTitle(rs.getString(1));
			article.setContent(rs.getString(2));
			article.setId(rs.getInt("id"));
			articleList.add(article);
		}
		rs.close();
		conn.close();
		return articleList;
	}
	/**
	 * 根据关键字获取文章列表
	 * @param keyid
	 * @param page
	 * @return
	 * @throws SQLException
	 */
	public List<Article> getArticleListById(String keyid, int page)
			throws SQLException {
		List<Article> articleList = new ArrayList();
		Connection conn = DBUtil.getInstance().getConnection();
		Article article;
		String sql = "SELECT title, IF(CHAR_LENGTH(content)>100,CONCAT(LEFT(content,100),'...'),content) FROM article where kid="
				+ keyid
				+ " order by updatetime desc  limit "
				+ (page - 1)
				* 20
				+ ",19";
		ResultSet rs = conn.createStatement().executeQuery(sql);
		System.out.println(sql);
		while (rs.next()) {
			article = new Article();
			article.setTitle(rs.getString(1));
			article.setContent(rs.getString(2));
			articleList.add(article);
		}
		return articleList;
	}
	
	/**
	 * 根据关键字获取文本信息
	 * @param keyword
	 * @param page
	 * @return
	 * @throws SQLException
	 */
	public List<Article> getArticleListByKeyword(String keyword, int page)
			throws SQLException {
		List<Article> articleList = new ArrayList();
		Connection conn = DBUtil.getInstance().getConnection();
		Article article;
		String sql = "SELECT title, IF(CHAR_LENGTH(content)>100,CONCAT(LEFT(content,100),'...'),content) FROM article where title like '%"
				+ keyword
				+ "%' order by updatetime desc limit "
				+ (page - 1)
				* 20 + ",19";
		ResultSet rs = conn.createStatement().executeQuery(sql);
		while (rs.next()) {
			article = new Article();
			article.setTitle(rs.getString(1));
			article.setContent(rs.getString(2));
			articleList.add(article);
		}
		conn.close();
		return articleList;
	}
	

	public Article getArticleById(int id) throws SQLException{
		Article article = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select title,content from article where id=");
		sb.append(id);
		Connection conn = DBUtil.getInstance().getConnection();
		PreparedStatement psmt = conn.prepareStatement(sb.toString());
		ResultSet rs = psmt.executeQuery();
		while (rs.next()) {
			article = new Article();
			article.setTitle(rs.getString(1));
			article.setContent(rs.getString(2));
			article.setId(id);
		}
		conn.close();
		psmt.close();
		return article;
	}

}
